using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using MOD;

namespace DOM
{
   public  class SectionDOM
    {
       public bool InsertSection(string teacherId, CourseInfo info)
       {
           OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
           //try
           //{

               OleDbCommand cmd = new OleDbCommand();
               cmd.Connection = con;
               cmd.CommandText = "INSERT INTO [section] (teacherId,courseId,courseName,credit) VALUES (@teacherId,@courseId,@courseName,@credit)";
               
               cmd.Parameters.AddWithValue("@teacherId", teacherId);
               cmd.Parameters.AddWithValue("@courseId", info.CourseId);
               cmd.Parameters.AddWithValue("@courseName", info.CourseName);
               cmd.Parameters.AddWithValue("@credit", info.Credit);
               using(con)
               {
                    con.Open();
                    cmd.ExecuteNonQuery();
               }
               con.Close();
               return true;
           //}
           //catch (Exception )
           //{
           //    return false;

           //}
       }

       public List<CourseInfo> SelectCourse(string id)
       {
          List <CourseInfo> info = new List<CourseInfo>();
           OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
           //try
           //{
               //con.Open();
               OleDbCommand cmd = new OleDbCommand();
               cmd.Connection = con;
               cmd.CommandText = "Select * From [course] WHERE courseId = @id";
               cmd.Parameters.AddWithValue("@id", id);

               using (con)
               {
                   con.Open();

                   OleDbDataReader reader = cmd.ExecuteReader();
                   while (reader.Read())
                   {

                       CourseInfo cou = new CourseInfo();
                       cou.CourseId = (string)reader["courseId"];
                       cou.CourseName = (string)reader["courseName"];
                       cou.Credit = (string)reader["credit"];
                       info.Add(cou);
                   }
               }
               con.Close();
               return info;
           //}
           //catch(Exception)
           //{
           //    return info;
           //}
       }

       public bool DeleteSection(Int32 id)
       {
           OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
            //try
            //{
                con.Open();
                string queryStr = "DELETE * FROM  [section] WHERE sectionId=@id";
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = con;
                cmd.CommandText = queryStr;
                cmd.Parameters.AddWithValue("@id", id);
                cmd.ExecuteNonQuery();
                con.Close();
                return true;
            //}
            //catch (Exception)
            //{
            //    return false;
            //}
        }

       public bool UpdataSection(string sectionId, string courseId, string teacherId, int counts, string room)
       {
           OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
           try
           {
               con.Open();
               string queryStr = "UPDATE section SET sectionId=@sectionId,courseId=@courseId, teacherId =@teacherId,counts=@counts,room=@room";
               OleDbCommand cmd = new OleDbCommand();
               cmd.CommandText = queryStr;    
               cmd.Parameters.AddWithValue("@courseId", courseId);
               cmd.Parameters.AddWithValue("@teacherId", teacherId);
              
               cmd.ExecuteNonQuery();
               con.Close();
               return true;

           }
           catch
           {
               return false; 
           }
       }

       public DataTable SelectById(string id)
       {
           OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
           con.Open();
           string queryStr = "SELECT * FROM section WHERE sectionId = id";
           OleDbDataAdapter result = new OleDbDataAdapter(queryStr, con);
           DataTable dt = new DataTable();
           result.Fill(dt);
           con.Close();
           return dt;
       }
       public DataTable SelectAll()
       {
           OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
           con.Open();
           string queryStr = "SELECT * FROM [section]";
           OleDbDataAdapter result = new OleDbDataAdapter(queryStr, con);
           DataTable dt = new DataTable();
           result.Fill(dt);
           con.Close();
           return dt;
       }
       public List<SectionInfo> SelectSection(string id)
       {
           List<SectionInfo> info = new List<SectionInfo>();
           OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data\selectCourse.mdb");
           
           OleDbCommand cmd = new OleDbCommand();
           cmd.Connection = con;
           cmd.CommandText = "Select * From [section] WHERE sectionId = @id";
           cmd.Parameters.AddWithValue("@id", id);

           using (con)
           {
               con.Open();

               OleDbDataReader reader = cmd.ExecuteReader();
               while (reader.Read())
               {

                   SectionInfo cou = new SectionInfo();
                   cou.SectionId = (string)reader["sectionId"].ToString();
                   cou.TeacherId = (string)reader["teacherId"];
                   cou.CourseId = (string)reader["courseId"];
                   cou.CourseName = (string)reader["courseName"];
                   cou.Credit = (string)reader["credit"];
                   info.Add(cou);
               }
           }
           con.Close();
           return info;
          
       }
      
    }
}
